package web.java.b_Statement;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

import org.junit.Test;

import web.java.z_JDBCUtil.JDBCUtil;

/**
 * 数据库中表的操作
 * */

public class Demo01_DDL {

	String url="jdbc:mysql://localhost:3306/shop";
	String user="root";
	String password="root";
	
	/**
	 * 创建一个表
	 * */
	@Test
	public void createStatement() throws ClassNotFoundException, SQLException{
		//1、连接数据库
		Class.forName("com.mysql.jdbc.Driver");
		Connection conn=DriverManager.getConnection(url,user,password);
		//2、创建DDL语句驱动statement对象
		Statement stmt=conn.createStatement();
		//3、写建表的sql语句
		String sql="create table test1( id int primary key auto_increment, name varchar(6), age int )";
		//4、执行DDL语句
		int result=stmt.executeUpdate(sql);
		System.out.println("共影响了"+result+"行");
		stmt.close();
		conn.close();
	}
	/**
	 * 对表进行增加操作
	 * */
	@Test
	public void add(){
		//1、连接数据库
		try{
			Class.forName("com.mysql.jdbc.Driver");
		}catch(ClassNotFoundException e){
			e.printStackTrace();
		}
		Connection conn=null;
		Statement stmt=null;
		try{
			conn=DriverManager.getConnection(url,user,password);
			stmt=conn.createStatement();
			String sql="insert into test1(name,age) values('caint',26)";
			int result=stmt.executeUpdate(sql);
			System.out.println("共影响了"+result+"行");
		}catch(SQLException e){
			System.out.println("连接数据库失败！"+e.getMessage());
		}finally{
			if(stmt!=null){
				try {
					stmt.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					throw new RuntimeException(e);
				}
				if(conn!=null){
					try {
						conn.close();
					} catch (SQLException e) {
						// TODO Auto-generated catch block
						throw new RuntimeException(e);
					}
				}
			}
		}
	}
	/**
	 * 修改表的数据
	 * */
	@Test
	public void modify(){
		try{
			Class.forName("com.mysql.jdbc.Driver");
		}catch(ClassNotFoundException e){
			e.printStackTrace();
		}
		Connection conn=null;
		Statement stmt=null;
		try{
			conn=DriverManager.getConnection(url,user,password);
			stmt=conn.createStatement();
			String name="高威";
			int age=16;
			String sql="UPDATE test1 SET NAME='"+name+"',age="+age+" WHERE id=1";
			int result=stmt.executeUpdate(sql);
			System.out.println("共影响了"+result+"行");
		}catch(SQLException e){
			System.out.println("数据库修改出错！"+e.getMessage());
		}finally{
			if(stmt!=null){
				try {
					stmt.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					throw new RuntimeException(e);
				}
				if(conn!=null){
					try {
						conn.close();
					} catch (SQLException e) {
						// TODO Auto-generated catch block
						throw new RuntimeException(e);
					}
				}
			}
		}
	}
	/**
	 * 删除表数据
	 * */
	@Test
	public void delete(){
		//连接数据库
		Connection conn=JDBCUtil.connection();
		Statement stmt=null;
		try {
			stmt=conn.createStatement();
			String sql="delete from test1 where id=2";
			int result=stmt.executeUpdate(sql);
			System.out.println("共影响了"+result+"行");
			JDBCUtil.closeConn(conn, stmt);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}
}
